"""
Case Type   : 事务-ustore
Case Name   : 事务->子事务->Create user->子回滚->主提交->kill数据库
Create At   : 2022/1/14
Owner       : @kyleze
Description :
    1.创建用户
    2.kill数据库
    3.查询系统表
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    5.成功
History     :
"""
import os
import sys
import unittest

from yat.test import macro
from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class USTORE(unittest.TestCase):
    def setUp(self):
        self.pri_user = Node('PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.log = Logger()
        self.table = 't_ustore_trans_0005'
        self.user1 = 'user_ustore_trans_0005_01'
        self.user2 = 'user_ustore_trans_0005_02'
        text = f'---{os.path.basename(__file__)} start---'
        self.log.info(text)
    
    def testunit_1(self):
        text = '----step1:创建用户;expect:成功----'
        self.log.info(text)
        q_str = f'''start transaction;
        create user {self.user1} identified by '{macro.COMMON_PASSWD}';
        savepoint point0005;
        create user {self.user2} identified by '{macro.COMMON_PASSWD}';
        rollback to savepoint point0005;
        create table {self.user1}.{self.table}(i int) 
        with (storage_type=ustore);
        commit
        '''
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, q_res, '执行失败: ' + text)
        self.assertIn('CREATE ROLE', q_res, '执行失败: ' + text)

        text = '----执行vacuum;expect:成功----'
        self.log.info(text)
        q_str = 'vacuum;'
        self.log.info(q_str)
        q_res = self.pri_sh.execut_db_sql(q_str)
        self.log.info(q_res)
        self.assertIn('VACUUM', q_res, '执行失败: ' + text)

        text = '----step2:kill-重启数据库;expect:成功----'
        self.log.info(text)
        result = self.pri_sh.restart_db_cluster()
        self.log.info(result)
        status = self.pri_sh.get_db_cluster_status()
        self.assertTrue('Degraded' in status or 'Normal' in status)

        text = '----step3:查询用户;expect:成功----'
        self.log.info(text)
        sql = f"select usename from pg_user where usename like 'user_ustore_trans_0005%' order by usename;"
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(f'{self.user1}', result, '执行失败: ' + text)
        self.assertIn('1 row', result, '执行失败: ' + text)

    def tearDown(self):
        text = '----step4:清理环境;expect:成功----'
        self.log.info(text)
        sql = f'drop table {self.user1}.{self.table} cascade;' \
              f'drop owned by {self.user1} cascade;drop user {self.user1}'
        result = self.pri_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      '执行失败: ' + text)
        self.assertIn('DROP ROLE', result, '执行失败: ' + text)
        text = f'---{os.path.basename(__file__)} end---'
        self.log.info(text)
